<?php
namespace app\index\controller;

use think\View;
use think\Db;
use think\Request;
use think\File;



class Excel extends \think\Controller
{
	// 列表页-页面
    public function loadIndex()
    {
        $view = new View();
        return $view->fetch('index');
    }

	// 列表页-数据
	public function indexLoad()
	{
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
		$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 15;

		$result = array();

		$result["total"] = Db::table('jdf_excel')->count();

		$bagin_record = ($page - 1) * $rows;
		$result["rows"] = Db::table('jdf_excel')->order('id desc')->limit($bagin_record, $rows)->select();

		exit(json_encode($result));
	}

	// 上传文件页面
	public function import(){
		$view = new View();
        return $view->fetch('add');
	}

	// 异步上传Excel文件到服务器
	public function uploadExcel(){

		$r = array();
		$r['status'] = true;

		if(!isset($_FILES) || empty($_FILES) || empty($_FILES['importFile']))
		{
			$r['status'] = false;
			$r['errMsg'] = 'Post data is empty';
			exit(json_encode($r));
		} else {
			//var_dump($_FILES);exit;

			$file = array();
			$file = $_FILES['importFile'];
			unset($_FILES);

			if(0 != $file['error']) {
				$r['status'] = false;
				$r['errMsg'] = '上传错误:error=0';
				exit(json_encode($r));
			}

			// excel文件保存目录
			$savaPath = $this->getSavePath();

			$f = new File($file['tmp_name']);

			$f->setUploadInfo($file);

			$fileSave = $f->move($savaPath, true);

			if($fileSave) {
				$r['files'] = array(
					'name' => $f->getInfo('name'),
					'savaPath' => $fileSave->getSaveName(),
				);
			} else {
				$r['status'] = false;
				$r['errMsg'] = $fileSave->getError();
			}

		}

		exit(json_encode($r));

	}

	// 格式化excel表格中日期数据
	public function formatDate($date = ''){
		if(empty($date)) {
			return '0000-00-00';
		}


		if(substr_count($date, '/') == 1 || substr_count($date, '\\') == 1 ) {
			if(strlen($date) == 8) { // 2017/211
				return substr($date,0,4).'-0'.substr($date,5,1).'-'.substr($date,-2);
			} elseif(strlen($date) == 7) { // 2017/23
				return substr($date,0,4).'-0'.substr($date,5,1).'-0'.substr($date,-1);
			}
		} elseif (substr_count($date, '-') == 2) {
			$t = explode('-', $date);
			$t1[0] = $t[2];
			$t1[1] = $t[0];
			$t1[2] = $t[1];
			return date("Y-m-d",strtotime(implode('-', $t1)));
		}
		return '0000-00-00';
	}

	// 导入Excel数据
	public function doIt() {
		// 返回数据初始化
		$res = array();
		$res['code'] = false;

		$request = Request::instance();
		$excelId = $request->get('excelId');

		if(empty($excelId)){
			$res['errMsg'] = "参数有误!";
			exit(json_encode($res));
		}

		// 处理excel表格数据初始化
		$data1 = array();
		$data1['id'] = $excelId;
		$data1['file_rows'] = 0;
		$data1['file_columns'] = 0;
		$data1['file_status'] = 2; // 导入成功
		$data1['import_date'] = date("Y-m-d H:i:s");
		$data1['import_rows'] = 0;

		$result = Db::name('excel')->where(array('id' => $excelId, 'file_status' => 1))->find();

		if(empty($result)){
			$res['errMsg'] = "文件不存在!";

			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}

		$uploadfile = $this->getSavePath().$result['import_path'];


		if (!file_exists($uploadfile)) {
			$res['errMsg'] = "文件不存在!";

			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}

		require_once EXTEND_PATH.'/phpexcel/PHPExcel/IOFactory.php';

		$type = strtolower(pathinfo($uploadfile , PATHINFO_EXTENSION));

		if($type=='xlsx'||$type=='xls' ){
			$reader = \PHPExcel_IOFactory::createReader('Excel2007'); // 读取 excel 文档
		}else if( $type=='csv' ){
			$reader = \PHPExcel_IOFactory::createReader('CSV'); // 读取 excel 文档
		}else{
			$res['errMsg'] = "不支持上传文件的类型!";
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);

			exit(json_encode($res));
		}

		$PHPExcel = $reader->load($uploadfile); // 文档名称
		$objWorksheet = $PHPExcel->getActiveSheet();
		$highestRow = $objWorksheet->getHighestRow(); // 取得总行数
		$highestColumn = $objWorksheet->getHighestColumn(); // 取得总列数

		$data1['file_rows'] = $highestRow;
		$data1['file_columns'] = $highestColumn;

		// 禁止一次性处理数据超过1000条
		if($highestRow >= 1000) {
			$res['errMsg'] = "一次性导入员工数不能超过1000个!";
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}

		// 禁止处理数据格式不对
		if($highestColumn != 'N') {
			$res['errMsg'] = "导入文件数据格式不对,防止数据错乱,导入终止! 请查阅导入模板的数据格式定义!";
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}

		$dataSet = array();
		$dataSet = $objWorksheet->toArray();
		if(empty($dataSet)) {
			$res['errMsg'] = "无法获取文件内容!";
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}
		unset($objWorksheet);

		// 检查文件的表头, 以判断数据格式是否与预定一致, 避免数据不对
		$headSet = $dataSet[0];

		foreach($headSet as $k => $v) {
			switch($k) {
				case 0:
					if($v != '部门') {
						$res['errMsg'] = "数据格式错误,第1行A列不是部门!";
					}
				break;
				case 1:
					if($v != '科室') {
						$res['errMsg'] = "数据格式错误,第1行B列不是科室!";
					}
				break;
				case 2:
					if($v != '班组') {
						$res['errMsg'] = "数据格式错误,第1行C列不是班组!";
					}
				break;
				case 3:
					if($v != '工号') {
						$res['errMsg'] = "数据格式错误,第1行D列不是工号!";
					}
				break;
				case 4:
					if($v != '姓名') {
						$res['errMsg'] = "数据格式错误,第1行E列不是姓名!";
					}
				break;
				case 5:
					if($v != '入司日期') {
						$res['errMsg'] = "数据格式错误,第1行F列不是入司日期!";
					}
				break;
				case 6:
					if($v != '工序1') {
						$res['errMsg'] = "数据格式错误,第1行G列不是工序1!";
					}
				break;
				case 7:
				case 9:
				case 11:
				case 13:
					if($v != '认证日期') {
						$res['errMsg'] = "数据格式错误,第1行H/J/L/N列不是认证日期!";
					}
				break;
				case 8:
					if($v != '工序2') {
						$res['errMsg'] = "数据格式错误,第1行I列不是工序2!";
					}
				break;
				case 10:
					if($v != '工序3') {
						$res['errMsg'] = "数据格式错误,第1行K列不是工序3!";
					}
				break;
				case 12:
					if($v != '工序4') {
						$res['errMsg'] = "数据格式错误,第1行M列不是部门!";
					}
				break;
				default:
					$res['errMsg'] = "数据格式错误,请联系技术人员!";
				break;
			}
		}

		if(isset($res['errMsg']) && !empty($res['errMsg'])) {
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}

		unset($dataSet[0]);

		$memberData = array();
		$trainingData = array();
		$_employee_no = array(); // 记录这批数据的员工编号

		foreach($dataSet as $k => $v) {
			if(!in_array($v[3], $_employee_no)) {
				array_push($_employee_no, $v[3]);
			}

			$memberData[$v[3]] = array(
				'departs' => $v[0],
				'departs_section' => $v[1],
				'section_room' => $v[2],
				'employee_no' => $v[3],
				'employee_name' => $v[4],
				'join_date' => $this->formatDate($v[5]),
			);

			// 认证日期 = 考核日期
			$trainingData[$v[3]][] = array(
				'employee_id' => 0,
				'train_position' => !empty($v[6]) ? $v[6] : '',
				'valid_date' => !empty($v[7]) ? $this->formatDate( $v[7]) : '0000-00-00',
			);
			$trainingData[$v[3]][] = array(
				'employee_id' => 0,
				'train_position' => !empty($v[8]) ? $v[8] : '',
				'valid_date' => !empty($v[9]) ? $this->formatDate( $v[9]) : '0000-00-00',
			);
			$trainingData[$v[3]][] = array(
				'employee_id' => 0,
				'train_position' => !empty($v[10]) ? $v[10] : '',
				'valid_date' => !empty($v[11]) ? $this->formatDate( $v[11]) : '0000-00-00',
			);
			$trainingData[$v[3]][] = array(
				'employee_id' => 0,
				'train_position' => !empty($v[12]) ? $v[12] : '',
				'valid_date' => !empty($v[13]) ? $this->formatDate( $v[13]) : '0000-00-00',
			);

		}
		//dump($objWorksheet->toArray());exit;
		// 应该导入的员工数量
	$data1['memo'] = "文件记录总数:".count($memberData)."; ";
		// 查这批employee_no 是否已存在
		$where['employee_no'] = array('in', $_employee_no);
		$result = Db::name('employee')->field('employee_no')->where($where)->select();

		$result2 = array(); // 已重复添加的员工号*****************
		if(!empty($result)){
			$result2 = array_map('current', $result);
		}

		if(count($memberData) == count($result2)) {
			$res['errMsg'] = '导入文件数据应该早就全部导入成功过!';
			$data1['file_status'] = 4; // 文件作废
			$data1['memo'] = '文件作废:'.$res['errMsg'];
			Db::name('excel')->update($data1);
			exit(json_encode($res));
		}
	$data1['memo'] .= "无效员工编号数:".count($result2)."(均被使用), 它们是:".implode(",",$result2)."; ";

		// 去重
		foreach($result2 as $eno) {
			unset($memberData[$eno]);
			unset($trainingData[$eno]);
		}

		// 开始入库
		// 入员工库
		$insertNum = Db::name('employee')->insertAll($memberData);

	$data1['import_rows'] = $insertNum;
	$data1['memo'] .= "成功导入员工数:".$insertNum."; ";


		// 准备一次性插入到training表中
		$trainingAllData = array();

		if($insertNum) { // 添加成功
			$insertEmployeeNoItems = array_keys($memberData);

			$where['employee_no'] = array('in', $insertEmployeeNoItems);
			$result = Db::name('employee')->field('employee_no,id')->where($where)->select();

			$employeeNoToEid = array();
			if(!empty($result)) {
				foreach($result as $val) {
					$employeeNoToEid[$val['employee_no']] = $val['id'];
				}
			}
			if(empty($employeeNoToEid)){
				$data1['memo'] .= "导入员工数培训记录数:".count($employeeNoToEid)."; ";
			} else {
				// 添加培训数据
				$count_train = 0;

				foreach($trainingData as $k1 => $v1) {
					foreach($v1 as $v2) {
						$trainingAllData[] = array(
							'employee_id' => $employeeNoToEid[$k1],
							'train_position' => $v2['train_position'],
							'valid_date' => $v2['valid_date'],
							'create_timestamp' => date("Y-m-d H:i:s"),
							'recorder' =>  'script',
							);
					}
				}


				if(!empty($trainingAllData)) {
					$insertTrainingNum = Db::name('training')->insertAll($trainingAllData);
					$data1['memo'] .= "成功导入员工培训记录数:".$insertTrainingNum."; ";
				}
				Db::name('excel')->update($data1);


			}
		} else { // 添加失败
			$data1['memo'] .= ' 文件已处理!';
			Db::name('excel')->update($data1);

		}
		$res['code'] = true;
		$res['msg'] = '导入成功!';
		exit(json_encode($res));

	}

	// 保存上传Excel窗口的表单数据
	public function save(){

		$request = Request::instance();
		$_post = $request->post();

		if(empty($_post['import_no'])) {
			$_post['import_no'] = date('YmdHis',time());
		}
		$name = explode(',', $_post['filesName']);
		$path = explode(',', $_post['filesPath']);

		if(empty($name) || empty($path) || count($name) != count($path)) {
			exit(json_encode(array('errorMsg'=>'未上传文件.')));
		}

		$data = array();
		foreach( $name as $k => $v ) {
			array_push($data, array(
				'import_no' => $_post['import_no'],
				'original_name' => $v,
				'import_name' => basename($path[$k]),
				'import_path' => $path[$k],
				'import_datetime' => date('Y-m-d H:i:s'),
				'memo' => $_post['memo']
			));
		}

		$r = Db::name('excel')->insertAll($data);

		if ($r){
			exit(json_encode(array('success'=>true,'msg' =>'文件上传成功')));
		} else {
			exit(json_encode(array('errorMsg'=>'Some errors occured.')));
		}
	}

	// 扫码获得员工信息
	public function employeeInfo(){

		$request = Request::instance();

		$employee_no = $request->get('employee_no');

		$userInfo = Db::name('employee')->where(array('employee_no' => $employee_no))->find();

		$view = new View();
		$view->employee_no = $userInfo['employee_no'];
		$view->employee_name = $userInfo['employee_name'];
		//$view->employee_addtime = substr($userInfo['create_timestamp'],0,10);
		$view->join_date = $userInfo['join_date'];
		$view->departs = $userInfo['departs'];
		$view->departs_section = $userInfo['departs_section'];
		$view->section_room = $userInfo['section_room'];

		if(empty($userInfo['avatar'])){
			$view->avatar = '<img width="160px" height="160px" src="/static/images/default.jpg">';
		} else {
			$view->avatar = '<img width="200px" height="200px" src="/static/images/default.jpg">';
		}

        return $view->fetch('info');


	}


	// 保存用户认证信息
	public function saveUserTrain(){

		$request = Request::instance();

		$_post = $request->post();

		$_post['create_timestamp'] = date("Y-m-d H:i:s",time());


		Db::name('training')->insert($_post);

		$userId = Db::name('training')->getLastInsID();

		if ($userId){
			exit(json_encode(array('success'=>true)));
		} else {
			exit(json_encode(array('errorMsg'=>'Some errors occured.')));
		}
	}

	// 获得用户信息
	public function getUserData(){

		$request = Request::instance();

		$employee_id = $request->get('employee_id');

		$row = $this->getEmployeeInfoByEmployeeNo(array('id' => $employee_id, 'status' => 1));;

		echo json_encode($row);
	}


	// 获得Excel 文件上传路径
	public function getSavePath(){
		return  ROOT_PATH.'excel'.DS;
	}


	// 定时执行任务
	public function cronAddExcelFile(){

		$_td = date("Y-m-d");
		$_r = Db::query('select count(*) as cnt from jdf_excel where SUBSTR(import_datetime, 1, 10) = ?',[$_td]);

		if($_r[0]['cnt'] > 100) {
			exit(json_encode(array('code'=>false,'msg' =>'with maximun daily visit of 100 times.')));
		}


		$memo = null;

		if (input('?get.memo')) {
			$memo = 'GET:'.input('get.memo');
		}

		if (input('?post.memo')) {
			$memo = 'POST:'.input('post.memo');
		}

		if(empty($memo)) {
			$memo = "默认数据";
		}

		$import_no = input('get.import_no') ? input('get.import_no') : ( input('post.import_no') ? input('post.import_no') : date("YmdHis") );
		$original_name = input('get.original_name') ? input('get.original_name') : ( input('post.original_name') ? input('post.original_name') : substr($import_no,-8).'-Microsoft Excel 工作表.xlsx' );
		$import_name = input('get.import_name') ? input('get.import_name') : ( input('post.import_name') ? input('post.import_name') : $import_no.'-88bac70718afa07e9113717f9a073f36.xlsx' );;
		$import_path = '20180209/88bac70718afa07e9113717f9a073f36.xlsx';
		$import_datetime = date("Y-m-d H:i:s");


		$d = [];
		$d['import_no'] = $import_no;
		$d['original_name'] = $original_name;
		$d['import_name'] = $import_name;
		$d['import_path'] = $import_path;
		$d['import_datetime'] = $import_datetime;
		$d['memo'] = $memo;

		$r = Db::name('excel')->insert($d);

		if ($r){
			exit(json_encode(array('code'=>true,'msg' =>'数据上传成功')));
		} else {
			exit(json_encode(array('code'=>false,'msg'=>'数据上传失败,有错误发生.')));
		}

	}

}
